package cn.com.nes.common.util;

import cn.com.nes.site.listener.ExcelListener;
import cn.hutool.core.io.IoUtil;
import cn.hutool.poi.excel.ExcelUtil;
import cn.hutool.poi.excel.ExcelWriter;
import cn.hutool.poi.excel.StyleSet;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.EasyExcelFactory;
import com.alibaba.excel.write.handler.SheetWriteHandler;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * Excel工具类
 */

public class ExcelUtils {

    private static final int MAX_WIDTH = 50;

    private static final SimpleDateFormat SDF = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

    /**
     * 导入文件 - 单sheet页
     *
     * @param inputStream // 导入文件inputStream
     * @param clazz       // 接参实体类 class
     * @param sheetNo     // 目标sheet页索引
     * @param listener    // 解析文件监听
     */
    public static void importExcel(InputStream inputStream, Class clazz, int sheetNo,
                                   ExcelListener listener) throws Exception {
        EasyExcel.read(inputStream, clazz, listener).sheet(sheetNo).doRead();
    }

    /**
     * 导出excel模板 - 单sheet页
     *
     * @param header            // 头信息
     * @param sheetWriteHandler // 文件处理程序,这里自定义导出文件逻辑,需要单独实现
     * @param response          // HttpServletResponse
     * @param sheetName         // sheet页名称
     * @param excelName         // 导出excel文件名称
     */
    public static void exportExcel(List<List<String>> header, SheetWriteHandler sheetWriteHandler,
                                   HttpServletResponse response, String sheetName, String excelName) throws Exception {
        // 构造导出文件流
        ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream();
        EasyExcelFactory.write(byteArrayOutputStream)
                .head(header)
                .registerWriteHandler(sheetWriteHandler)
                .sheet(sheetName)
                .doWrite(new ArrayList<>());

        // 设置输出文件名称
        String fileName = URLEncoder.encode(excelName, "UTF-8").replaceAll("\\+", "%20");
        response.setHeader("Access-Control-Expose-Headers", "Content-Disposition");
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        response.setHeader("Content-disposition", "attachment;filename*=" + fileName + ".xlsx");
        // 输出
        ServletOutputStream servletOutputStream = response.getOutputStream();
        servletOutputStream.write(byteArrayOutputStream.toByteArray());
        byteArrayOutputStream.close();
        servletOutputStream.flush();
        servletOutputStream.close();
    }

    public static void exportData(String[] fieldList, String[] titleList, String[] typeList, List<?> dataList,
                                  String title, HttpServletResponse response, String fileName, boolean isNo) throws Exception {
        // 通过工具类创建writer，默认创建xls格式
        ExcelWriter writer = ExcelUtil.getWriter();
        if (isNo) {
            String[] fieldList1 = new String[fieldList.length + 1];
            fieldList1[0] = "No";
            System.arraycopy(fieldList, 0, fieldList1, 1, fieldList1.length - 1);
            fieldList = fieldList1;

            String[] titleList1 = new String[titleList.length + 1];
            titleList1[0] = "序号";
            System.arraycopy(titleList, 0, titleList1, 1, titleList1.length - 1);
            titleList = titleList1;

            String[] typeList1 = new String[typeList.length + 1];
            typeList1[0] = "String";
            System.arraycopy(typeList, 0, typeList1, 1, typeList1.length - 1);
            typeList = typeList1;
            int number = 1;
            //添加序号(排除合计行)
            for (int i = 0; i < dataList.size(); i++) {
                boolean isExclude = false;
                Object o = dataList.get(i);
                Class<?> aClass = dataList.get(i).getClass();
                Field[] declaredFields = aClass.getDeclaredFields();
                for (Field fields : declaredFields) {
                    fields.setAccessible(true);
                    if ("xzqhmc".equals(fields.getName())) {
                        Object o1 = fields.get(o);
                        if ("合计".equals(o1)) {
                            isExclude = true;
                            break;
                        }
                    }
                    if ("equipmentCompanyName".equals(fields.getName())) {
                        Object o1 = fields.get(o);
                        if ("合计".equals(o1)) {
                            isExclude = true;
                            break;
                        }
                    }
                }
                if (!isExclude) {
                    Field field = declaredFields[0];
                    if ("No".equals(field.getName())) {
                        field.setAccessible(true);
                        field.set(o, String.valueOf(number));
                        number++;
                    }
                }
            }
        }

        //自定义标题别名
        if (titleList.length != fieldList.length) {
            throw new Exception("表头与字段没有对应");
        }
        //设置标题样式
        CellStyle titleStyle = setTitleStyle(writer);
        writer.merge(0, 0, 0, fieldList.length - 1, title, titleStyle);//合并单元格
        writer.passRows(1);//跳过一行
        if (isNo) {
            writer.addHeaderAlias("No", "序号");
        }
        for (int i = 0; i < fieldList.length; i++) {
            writer.addHeaderAlias(fieldList[i], titleList[i]);
        }
        //设置表头样式
        setHeadStyle(writer);
        writer.setOnlyAlias(true);//只显示表头
        // 一次性写出内容，使用默认样式，强制输出标题
        writer.write(dataList, true);

        //        writer.autoSizeColumnAll();//自适应列宽
        writer.setRowHeight(0, 35); //设置第一行行高
        //设置列宽
        for (int i = 0; i < fieldList.length; i++) {
            int final_length = 0;
            if (isNo) {
                if (i == 0) {
                    writer.setColumnWidth(1, 10);
                } else {

                    for (Object data : dataList) {
                        Class<?> aClass = data.getClass();
                        Field field = aClass.getDeclaredField(fieldList[i]);
                        Class<?> type = field.getType();
                        field.setAccessible(true);
                        Object o = field.get(data);
                        if (o != null && "java.util.Date".equals(type.getName())) {
                            o = SDF.format(o);
                        }
                        //计算字符串长度
                        int length = getStrLength(o);
                        if (length < MAX_WIDTH && length > 0) {
                            if (length > final_length) {
                                final_length = length;
                            }
                        } else {
                            final_length = 50;
                            break;
                        }
                    }
                    if (final_length == 0 || final_length < 15) {
                        final_length = 15;
                    }
                    writer.setColumnWidth(i, final_length);
                }
            } else {
                for (Object data : dataList) {
                    Class<?> aClass = data.getClass();
                    Field field = aClass.getDeclaredField(fieldList[i]);
                    Class<?> type = field.getType();
                    field.setAccessible(true);
                    Object o = field.get(data);
                    if (o != null && "java.util.Date".equals(type.getName())) {
                        o = SDF.format(o);
                    }
                    //计算字符串长度
                    int length = getStrLength(o);
                    if (length < MAX_WIDTH && length > 0) {
                        if (length > final_length) {
                            final_length = length;
                        }
                    } else {
                        final_length = 50;
                        break;
                    }
                }
                if (final_length == 0 || final_length < 15) {
                    final_length = 15;
                }
                writer.setColumnWidth(i, final_length);
            }
        }
        //设置数据样式
        CellStyle cellStyle_tmp = writer.createCellStyle();//放在循环里面会报 You can define up to 4000 styles in a .xls workbook
        Font font = writer.createFont();//放在循环里面会报 Maximum number of fonts was exceeded
        for (int i = 0; i < dataList.size(); i++) {
            for (int j = 0; j < fieldList.length; j++) {
                Cell cell = writer.getCell(j, i + 2);
                CellStyle cellStyle = formatData(j, writer, typeList);
                CellStyle cellStyle1 = setDataStyle(cellStyle, font);
                cell.setCellStyle(cellStyle1);
            }
        }
        //response为HttpServletResponse对

         fileName = URLEncoder.encode(fileName, "UTF-8").replaceAll("\\+", "%20");
        response.setHeader("Access-Control-Expose-Headers", "Content-Disposition");
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        response.setHeader("Content-disposition", "attachment;filename*=" + fileName + ".xls");
        ServletOutputStream out = null;
        try {
            out = response.getOutputStream();
            writer.flush(out, true);
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            // 关闭writer，释放内存
            writer.close();
        }
        //此处记得关闭输出Servlet流
        IoUtil.close(out);
    }

    //格式化数据
    public static CellStyle formatData(int index, ExcelWriter writer, String[] typeList) {
        StyleSet styleSet = writer.getStyleSet();
        DataFormat dataFormat = writer.getWorkbook().createDataFormat();
        CellStyle cellStyle = null;
        String type = typeList[index];
        switch (type) {
            case "String":
                cellStyle = styleSet.getCellStyle();
                cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("General"));
                break;
            case "int":
                cellStyle = styleSet.getCellStyleForNumber();
                cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00"));
                break;
            case "percent":
                cellStyle = styleSet.getCellStyleForNumber();
                cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00%"));
                break;
            case "date":
                cellStyle = styleSet.getCellStyleForDate();
                cellStyle.setDataFormat(dataFormat.getFormat("yyyy-MM-dd HH:mm:ss"));
                break;
        }
        return cellStyle;
    }

    public static CellStyle setDataStyle(CellStyle cellStyle, Font font) {

        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER); //设置单元格的垂直对齐类型
        cellStyle.setAlignment(HorizontalAlignment.CENTER); //设置单元格的水平对齐类型

        cellStyle.setFillForegroundColor(HSSFColor.HSSFColorPredefined.WHITE.getIndex());
        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        cellStyle.setBorderLeft(BorderStyle.MEDIUM); //设置要用于单元格左侧边框的边框类型
        cellStyle.setBorderRight(BorderStyle.MEDIUM); //设置用于单元格右边框的边框类型
        cellStyle.setBorderBottom(BorderStyle.MEDIUM); //设置用于单元格底部边框的边框类型
        cellStyle.setBorderTop(BorderStyle.MEDIUM); //设置用于单元格顶部边框的边框类型

        font.setFontHeightInPoints((short) 11);
        font.setFontName("宋体");
        cellStyle.setFont(font);
        return cellStyle;
    }

    /**
     * 计算字符串长度
     * 汉字*2   其他*1
     *
     * @param o
     * @return
     */
    private static int getStrLength(Object o) {
        Pattern compile = Pattern.compile("[\u4e00-\u9fa5]");
        double length = 0;
        if (o == null) {
            return 4;
        }
        String s = o.toString();
        for (int i = 0; i < s.length(); i++) {
            char c = s.charAt(i);
            Matcher matcher = compile.matcher(String.valueOf(c));
            boolean b = matcher.find();
            if (b) {
                length += 2.3;
            } else {
                length += 1.5;
            }
        }
        return (int) Math.ceil(length);
    }

    //设置表头样式
    public static void setHeadStyle(ExcelWriter writer) {
        // 设置表头样式
        StyleSet style = writer.getStyleSet();
        CellStyle headCellStyle = style.getHeadCellStyle();
        headCellStyle.setVerticalAlignment(VerticalAlignment.CENTER); //设置单元格的垂直对齐类型
        headCellStyle.setAlignment(HorizontalAlignment.CENTER); //设置单元格的水平对齐类型

        headCellStyle.setFillForegroundColor(HSSFColor.HSSFColorPredefined.LIME.getIndex());
        headCellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        headCellStyle.setBorderLeft(BorderStyle.MEDIUM); //设置要用于单元格左侧边框的边框类型
        headCellStyle.setBorderRight(BorderStyle.MEDIUM); //设置用于单元格右边框的边框类型
        headCellStyle.setBorderBottom(BorderStyle.MEDIUM); //设置用于单元格底部边框的边框类型
        headCellStyle.setBorderTop(BorderStyle.MEDIUM); //设置用于单元格顶部边框的边框类型
        Font font = writer.createFont();
        font.setFontHeightInPoints((short) 12);
        font.setFontName("宋体");
        headCellStyle.setFont(font);
    }

    //设置标题样式
    public static CellStyle setTitleStyle(ExcelWriter writer) {
        CellStyle cellStyle = writer.getCellStyle();
        cellStyle.setFillForegroundColor(HSSFColor.HSSFColorPredefined.WHITE.getIndex());
        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        cellStyle.setBorderLeft(BorderStyle.MEDIUM); //设置要用于单元格左侧边框的边框类型
        cellStyle.setBorderRight(BorderStyle.MEDIUM); //设置用于单元格右边框的边框类型
        cellStyle.setBorderBottom(BorderStyle.MEDIUM); //设置用于单元格底部边框的边框类型
        cellStyle.setBorderTop(BorderStyle.MEDIUM); //设置用于单元格顶部边框的边框类型
        Font font = writer.createFont();
        font.setBold(true);
        font.setFontHeightInPoints((short) 16);
        font.setFontName("宋体");
        cellStyle.setFont(font);
        return cellStyle;
    }

}
